数据库应用开发技术实验报告

“数据库应用开发技术”实验报告

2020-2021学年 第二学期

实验名称： 学生姓名： 学 号： 专业班级： 所在学院： 指导教师： 职 称： 完成日期： 实验1 sql server 熟悉和数据库创建 实验目的 熟悉sql server 2005提供的服务管理器、企业管理器、查询分析器、客户端和服务器端网络实用工具等常用管理工具的使用。理解客户/服务器模式，理解面向连接与非面向连接的差别。理解交互式sql的工作机制。能够理解命名管道协议与tcp/ip协议的差别。能够登陆上sql server数据库服务器。

实验内容 1、启动sql server 服务。 2、打开sql server的企业管理器，连接上sql server服务器。展开左边树状窗口的各级结点，观察右边内容窗口的变化。 3、打开sql server的查询分析器，用use命令打开样例数据库pubs。 4、在查询窗口输入exec sp_help，运行后察看结果。 5、在查询窗口输入select * from authors ，运行后察看结果。

三、实验结果 3、当不确定当前所操作的是哪个数据库，可使用use来定位到某数据库。 4、

查询某张表的所有列。

实验2 简单查询 实验目的： 熟悉sql server的企业管理器和查询分析器的用户界面，掌握用企业管理器和查询分析器创建数据库，修改数据库和删除数据库的方法。

实验内容 分别使用sql server 2005企业管理器和t—sql语句，按下列要求创建、修改和删除用户数据库。 1、创建名称为company的数据库，数据库中包含一个数据文件，逻辑文件名为company_data，磁盘文件名为company_data.mdf，文件初始容量为5mb，最大容量为15mb，文件容量递增值为1mb；事务日志文件的逻辑文件名为company_log，磁盘文件名为company_log.ldf，文件初始容量为5mb，最大容量为10mb，文件容量递增值为1mb。 2、对该数据库进行修改：添加一个数据文件，逻辑文件名为company2_data，磁盘文件名为company2_data.ndf，文件初始容量为1mb，最大容量为5mb，文件容量递增值为1mb；将日志文件company_log的最大容量增加为15mb，文件容量递增值为2mb。 3、 在company数据库中添加一个文件组tempgroup，并向该文件组中添加一个容量为3mb，最大容量为10mb，递增量为1mb的数据文件，该数据文件的逻辑文件名为company3_data，磁盘文件名为company3_data.ndf。 4、在company数据库中删除数据文件company2_data。 删除数据库company。 采用默认设置创建数据库company。

实验结果 代码如下： CREATE DATABASE company ON PRIMARY (NAME=company_data, FILENAME=‘D:\zlj\company_data.mdf’, SIZE=5, MAXSIZE=15, FILEGROWTH=1) LOG ON (NAME=company_log, FILENAME=‘D:\zlj\company_log.ldf’, SIZE=5, MAXSIZE=10, FILEGROWTH=1) 2、代码如下： ALTER DATABASE company ADD FILE (NAME=company2_data, FILENAME=‘D:\zlj\company2_data.ndf’, SIZE=1, MAXSIZE=5, FILEGROWTH=1) ALTER DATABASE company MODIFY FILE (NAME=company_log, FILENAME=‘D:\zlj\company_log.ldf’, MAXSIZE=15, FILEGROWTH=2) 3、代码如下： ALTER DATABASE company ADD FILEGROUP tempgroup ALTER DATABASE company ADD FILE (NAME=company3_data, FILENAME=‘D:\zlj\company3_data.ndf’, SIZE=3, MAXSIZE=10, FILEGROWTH=1) 4、代码如下： ALTER DATABASE company REMOVE FILE company2_data 5、代码如下： DROP DATABASE company 6、代码如下： CREATE DATABASE company

实验3 创建和修改数据表 一、实验目的： 熟悉有关数据表的创建和修改等工作，理解数据库模式的概念，了解主键约束、外键约束、unique约束和check约束的创建和应用。要求学生熟练掌握使用企业管理器和t—sql语句create table、alter table及drop table语句对数据表进行管理。

实验内容 分别在sql server 2005企业管理器和在查询分析分析器中使用t—sql语句完成以下操作： 员工人事表employee emp no char(5) not null primary key 员工编号

emp_name varchar(10) not null

员工姓名

sex char(2) not null

性别

dept varchar(10) not null

所属部门

title varchar(10) not null

职称

date_hired datetime not null

雇佣日

birthday datetime null

生日

salary int not n ll

薪水

t lephone varchar(20) null

电话

addr varchar(50) null

住址

客户表customer cust_id char(5) not null primary key 客户号

cust_name varchar(20) not null

客户名称

addr varchar(40) not null

客户住址

tel_no varchar(20) not null

客户电话

zip char(6) null

邮政编码

销售主表sales order_no int not null primary key 订单编号

cust_id char(5) not null

客户号

sale_id char(5) not null

业务员编号

tot_amt numeric(9,2) not null

订单金额

order_date datetime not null

订货日期

销货明细表sale_item order_no int not null primary key 订单编号

prod_id char(5) not null pri ary key 产品编号

qty int not null

销售数量

unit_price numeric(7,2) not null

单价

order_ ate datetime null

订单日期

产品名称表product prod_id char(5) not null primary key 产品编号

prod_name varchar(20) not null

产品名称

2、在销售主表sales中添加字段“发票号码” invoice_no，char(10)，not null。 3、 添加外键约束： 在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段为员工表employee中的字段员工编号emp_no，约束名为fk_sale_id。 在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客户表customer中的字段客户号cust_id，约束名为fk_cust_id。 在销售明细表sale_item的订单编号字段order_no上添加外键约束，参照字段为销售主表sales中的字段订单编号order_no，约束名为fk_order_no。 在销售明细表sale_item的产品编号字段prod_id上添加外键约束，参照字段为产品名称表product中的产品编号字段prod_id，约束名为fk_prod_id。 4、添加核查约束： 将员工表employee中的薪水字段salary的值限定在1000至10000间，约束名为ck_salary。 将员工表employee中的员工编号字段emp_no设定为以“e”字母开头, 后面跟5位数的编号，约束名为ck_emp_no。 将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为ck_sex。 将销售主表sales中的发票号码字段invoice_no设定为以“i”字母开头，后面跟9位数的编号，约束名为ck_inno。 5、为销售主表sales中的字段发票号码invoice_no设置为唯一约束，约束名为un_inno。

实验结果 1、代码如下： CREATE DATABASE company USE company CREATE TABLE employee (emp_no char(5) not null primary key, emp_name varchar(10) not null, sex char(2) not null, dept varchar(10) not null, title varchar(10) not null, data_hired datetime not null, birthday datetime null, salary int not null, telephone varchar(20) null, addr varchar(50) null) CREATE TABLE customer (cust_id char(5) not null primary key, cust_name varchar(20) not null, addr varchar(40) not null, tel_no varchar(20) not null, zip char(6) null) CREATE TABLE sales (order_no int not null primary key, cust_id char(5) not null, sale_id char(5) not null, tot_amt numeric(9,2) not null, order_date datetime not null) CREATE TABLE sale_item (order_no int not null primary key, prod_id char(5) not null, qty int not null, unit_price numeric(7,2) not null, order_ate datetime null) CREATE TABLE product (prod_id char(5) not null primary key, prod_name varchar(20) not null) 2、代码如下： USE company ALTER TABLE sales ADD invoice_no char(10) not null; 3、代码如下： ALTER TABLE sales ADD CONSTRAINT FK_sale_id FOREIGN KEY (sale_id) REFERENCES employee(emp_no) ALTER TABLE sales ADD CONSTRAINT FK_cust_id FOREIGN KEY (cust_id) REFERENCES customer(cust_id) ALTER TABLE sale_item ADD CONSTRAINT FK_order_no FOREIGN KEY (order_no) REFERENCES sales(order_no) ALTER TABLE sale_item ADD CONSTRAINT FK_prod_id FOREIGN KEY (prod_id) REFERENCES product(prod_id) 4、代码如下： ALTER TABLE employee ADD CONSTRAINT ck_salary CHECK(salary>=1000 AND salary 10000 go 计算每一产品销售数量总和与平均销售单价。 SELECT prod_id , sum ( qty ) as 销售数量总和 , avg ( unit_price ) as 平均销售单价 from sale_item group by prod_id go 统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水（在group by 子句中使用cube关键字）。 SELECT dept , sex , avg ( salary ) from employee group by cube ( sex , dept ) go 统计各部门不同性别、或各部门或所有员工的平均薪水（在group by 子句中使用rollup关键字）。 SELECT dept , sex , avg ( salary ) from employee group by rollup ( sex , dept ) go 计算出一共销售了几种产品。 SELECT count ( prod_id ) as 产品数 from product 显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。 SELECT prod_id , sum ( qty * unit_price ) as 订购金额总和 from sale_item group by prod_id order by 订购金额总和 desc go 计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。 SELECT prod_id as 产品编号 , month ( order_date ) as 月份 , sum ( qty * unit_price ) as 销售金额总和 from sale_item group by month ( order_date ), prod_id order by month ( order_date ), prod_id go 10、查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。 SELECT sale_id as 业务员编号 , month ( order_date ) as 月份 , sum ( tot_amt ) as 业绩 from sales group by sale_id , month ( order_date ) order by sale_id , month ( order_date ) desc go

实验6 连接查询 一、实验目的 掌握使用连接的方法从多个表中查询数据。理解内连接、外连接（包括左外连接、右外连接和全外连接）、自身连接的概念和使用。要求学生熟练掌握在from子句和在where子句中指定连接条件的这两种方法。

二、实验内容 1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。 2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。 3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。 4、查询在每张订单中订购金额超过24000元的客户名及其地址。 5、查找有销售记录的客户编号、名称和订单总额。 6、每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。 7、查找在1997年中有销售记录的客户编号、名称和订单总额。 8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。

三、实验结果 1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。 SELECT a.emp_name, a.sex, a.title, a.salary,a.addr, b.emp_name, b.sex,b.title, b.salary, b.addr from employee AS a inner join employee AS b on(a.emp_no!=b.emp_no) and(a.emp_name>b.emp_name) and (a.addr=b.addr) 2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。 SELECT a.prod_id, b.prod_name, a.qty, a.unit_price from sale_item a, product b where a.prod_id=b.prod_id 3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。 SELECT product.prod_id,product.prod_name,qty,unit_price from product,sale_item where unit_price>2400 and product.prod_id=sale_item.prod_id order by sale_item.prod_id 4、查询在每张订单中订购金额超过24000元的客户名及其地址。 SELECT cust_name,addr from customer a,sales b where a.cust_id=b.cust_id and tot_amt>24000 5、查找有销售记录的客户编号、名称和订单总额。 SELECT a.cust_id, cust_name, sum(tot_amt) totprice from customer a, sales b where a.cust_id=b.cust_id group by a.cust_id,cust_name 6、每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。 SELECT cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty) From sales a, sale_item b Where a.order_no=b.order_no Group by cust_id,prod_id Order by cust_id,prod_id 7、查找在1997年中有销售记录的客户编号、名称和订单总额。 SELECT a.cust_id, cust_name,sum(tot_amt) totprice from customer a,sales b where a.cust_id=b.cust_id and convert(char(4),order_date,120)=‘1997’ group by a.cust_id,cust_name 8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。 左外连接： SELECT b.prod_id, a.prod_name, b.qty, b.unit_price from product a LEFT OUTER JOIN sale_item b on (a.prod_id=b.prod_id) AND b.unit_price>2400; 右外连接： SELECT b.prod_id, a.prod_name, b.qty, b.unit_price from product a RIGHT OUTER JOIN sale_item b on (a.prod_id=b.prod_id) AND b.unit_price>2400; 完整外部连接： SELECT b.prod_id, a.prod_name, b.qty, b.unit_price from product a FULL JOIN sale_item b on (a.prod_id=b.prod_id) AND b.unit_price>2400;

实验7 嵌套查询 一、实验目的 掌握select语句的嵌套使用，实现多表的复杂查询，进一步理解select语句的高级使用方法。

二、实验内容 1、由sales表中查找出销售金额最高的订单。 2、由sales表中查找出订单金额大于“e0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。 3、找出公司女业务员所接的订单。 4、找出目前业绩未超过200000元的员工。 5、在销售主表sales中查询销售业绩最高的业务员编号及销售业绩。 6、找出目前业绩超过232000元的员工编号和姓名。 7、查询订购的产品至少包含了订单10003中所订购产品的订单。 8、查询末承接业务的员工的信息。 三、实验结果 1、由sales表中查找出销售金额最高的订单。 SELECT * from sales where tot_amt=(select max(tot_amt) from sales) 2、由sales表中查找出订单金额大于“e0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。 SELECT sale_id,tot_amt from sales where tot_amt> all (select tot_amt from sales) 3、找出公司女业务员所接的订单。 SELECT sale_id,tot_amt from sales where sale_id in (select sale_id from employee where sex=‘女’) 4、找出目前业绩未超过200000元的员工。 SELECT * from employee where emp_no in (select sale_id from sales group by sale_id having sum(tot_amt)>2000) 5、在销售主表sales中查询销售业绩最高的业务员编号及销售业绩。 SELECT sale_id AS ’ 业务员编号 ',tot_amt AS ’ 销售业绩 ’ from sales where sale_id=( select top 1 sale_id from sales order by tot_amt desc ) 6、找出目前业绩超过232000元的员工编号和姓名。 SELECT emp_name , emp_no from employee where emp_no in ( select sale_id from sales group by sale_id having SUM ( tot_amt )> 232000 ) 7、查询订购的产品至少包含了订单10003中所订购产品的订单。 SELECT distinct * from sale_item where prod_id in ( select prod_id from sale_item where order_no = ‘10003’ ) 8、查询末承接业务的员工的信息。 SELECT * from employee a where not exists (select * from sales b where a.emp_no = b.sale_id )

实验8 数据更新 一、实验目的 熟练使用insert/delete/update语句进行表的更新操作。

二、实验内容 1、为各表添加若干条记录，必须符合实验二中设定的各种约束。 2、将每个员工的薪水上调10%。 3、删除sales表中作废的订单（其发票号码为‘i000000004’），其订货明细表中的数据也一并删除。 4、删除所有没有销售业绩的员工记录。 5、对那些只要有一笔销售业绩超过20000元的员工的薪水增加500元。

三、实验结果 1、为各表添加若干条记录，必须符合实验二中设定的各种约束。 INSERT INTO product VALUES(‘55555’,‘CD’) 2、将每个员工的薪水上调10%。 UPDATE employee set salary=salary*1.1 where title= ‘员工’; 3、删除sales表中作废的订单（其发票号码为‘i000000004’），其订货明细表中的数据也一并删除。 DELETE from sales where invoice_no=‘I000000004’ 4、删除所有没有销售业绩的员工记录。 DELETE employee where dept =‘业务’ and emp_no not in (select distinct sale_id from sales ) 5、对那些只要有一笔销售业绩超过20000元的员工的薪水增加500元。 UPDATE employee set salary =salary+500 where emp_no in (select distinct sale_id from sales where tot_amt >20000)